﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FrbaCommerce.Listado_Estadistico
{
    public partial class Listado : Form
    {
        
        private int user_id;



        public Listado(int user_id)
        {
            this.user_id = user_id;

            InitializeComponent();
        }

        private string cargarTrimestre(string campoAConsultar)
        {
            string trimestre = "";

            switch (comboBoxTrim.SelectedIndex)
            {
                case 0:
                    trimestre = "YEAR("+ campoAConsultar +") = " + maskedTBanio.Text + " AND (MONTH(" + campoAConsultar + ") = 1 OR MONTH(" + campoAConsultar + ") = 2 OR MONTH(" + campoAConsultar + ") = 3) ";
                    break;
                case 1:
                    trimestre = "YEAR(" + campoAConsultar + ") = " + maskedTBanio.Text + " AND (MONTH(" + campoAConsultar + ") = 4 OR MONTH(" + campoAConsultar + ") = 5 OR MONTH(" + campoAConsultar + ") = 6) ";
                    break;
                case 2:
                    trimestre = "YEAR(" + campoAConsultar + ") = " + maskedTBanio.Text + " AND (MONTH(" + campoAConsultar + ") = 7 OR MONTH(" + campoAConsultar + ") = 8 OR MONTH(" + campoAConsultar + ") = 9) ";
                    break;
                case 3:
                    trimestre = "YEAR(" + campoAConsultar + ") = " + maskedTBanio.Text + " AND (MONTH(" + campoAConsultar + ") = 10 OR MONTH(" + campoAConsultar + ") = 11 OR MONTH(" + campoAConsultar + ") = 12) ";
                    break;
            }

            return trimestre;
      
        }

        private void buttonBuscar_Click(object sender, EventArgs e)
        {
            string consulta = "";
            //Armo el string de filtros

            switch (comboBoxTipo.SelectedIndex)
            { 
                //
                case 0:
                    consulta = "pub_usuario_id as 'Usuario', COUNT(pub_usuario_id) "
                    + " FROM Monkey_Coders.Publicaciones"
                    + " WHERE " + cargarTrimestre("pub_fecha_inicio")
                    + " AND pub_visibilidad_cod = " + ((Generar_Publicacion.GenerarPublicacion.Visibilidad)comboBoxVisib.SelectedItem).id
                    + " AND pub_cod not in (SELECT com_publicacion_id FROM Monkey_Coders.Compras)"
                    + " GROUP BY pub_usuario_id ORDER BY COUNT(pub_usuario_id)";
                    
                    break;

                case 1:
                    consulta = "fac_usuario_id AS 'Usuario' , SUM(fac_total) AS 'Facturacion Total'"
                        + " FROM Monkey_Coders.Facturas"
                        + " WHERE " + cargarTrimestre("fac_fecha")
                        + " GROUP BY fac_usuario_id"
                        + " ORDER BY SUM(fac_total) DESC";
                    break;

                case 2:
                    consulta = "cal_vendedor_id as 'Vendedor' , AVG(cal_cant_estrellas) as 'Promedio'"
                        + " FROM Monkey_Coders.Calificaciones"
                        + " WHERE "
                        + cargarTrimestre("cal_fecha_calificacion")
                        + " GROUP BY cal_vendedor_id"
                        + " ORDER BY AVG(cal_cant_estrellas) DESC";
                    break;

                case 3:
                    consulta = "Compras.com_usuario_comprador_id AS 'ID Usuario', "
                        + " (SELECT COUNT(com_usuario_comprador_id) - COUNT(cal_usuario_id)"
                        + " FROM Monkey_Coders.Calificaciones"
                        + " WHERE cal_usuario_id = Compras.com_usuario_comprador_id AND "+ cargarTrimestre("cal_fecha_calificacion") + " ) AS 'Sin Calificar'"
                        + " FROM Monkey_Coders.Compras Compras"
                        + " WHERE " + cargarTrimestre("com_fecha")
                        + " GROUP BY Compras.com_usuario_comprador_id"
                        + " ORDER BY 'Sin Calificar' DESC";
                    break;

                default:
                    break;
            }

            dataGridView1.Columns.Clear();

            SqlConnection con = Program.obtenerConexion();

            SqlDataAdapter buscar = new SqlDataAdapter("SELECT TOP 5" + consulta, con);

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(buscar);
            DataSet ds = new DataSet();
            buscar.Fill(ds);
            dataGridView1.ReadOnly = true;
            dataGridView1.DataSource = ds.Tables[0];

        }

        private void Listado_Load(object sender, EventArgs e)
        {
            Generar_Publicacion.GenerarPublicacion publicacion = new Generar_Publicacion.GenerarPublicacion(user_id);
            publicacion.CargarVisibilidades(comboBoxVisib);

            comboBoxTipo.SelectedIndex = 0;
            comboBoxTrim.SelectedIndex = 0;
            comboBoxVisib.SelectedIndex = 0;
        }

        private void buttonLimpiar_Click(object sender, EventArgs e)
        {
            dataGridView1.Columns.Clear();
        }

        private void maskedTBanio_TextChanged(object sender, EventArgs e)
        {
            if (maskedTBanio.Text == "")
            {
                buttonBuscar.Enabled = false;
                maskedTBanio.BackColor = Color.Red;
            }
            else
            {
                buttonBuscar.Enabled = true;
                maskedTBanio.BackColor = Color.White;
            }
        }

        private void comboBoxTipo_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBoxTipo.SelectedIndex == 0)
            {
                labelVisib.Visible = true;
                comboBoxVisib.Visible = true;
            }
            else
            {
                labelVisib.Visible = false;
                comboBoxVisib.Visible = false;
            }
        }

    }
}
